//	AUTHOR:			ENRICO CANTONI
//	CREATED:		MARCH 17, 2017
//	MODIFIED: 		FEBRUARY 11, 2018
//	DESCRIPTION:	Replace values of variables in a varlist based on
//					values of a single variable from a different varlist.

********************************************************************************
********************************************************************************

cap program drop fix_excel
program define fix_excel, nclass
	syntax using/, Sheet(string) Matching(varlist) ///
		[Fixing(varlist) matchall matchmiss xwalk]

	// Save data on memory
	tempfile temp_master
	save `temp_master'

	// Import excel file w/ fixing data
	import_excel `using', clear sheet(`sheet') firstrow
	foreach v in `matching' {
		cap confirm var `v'
		if _rc {
			di in red "Variable `v' does not exist in `using'"
			STOP
		}
	}

	// Check there are either vars to be fixed or obs to be dropped
	local fix = 1
	if "`fixing'" == "" {
		cap confirm numeric var drop
		if _rc {
			di in red "No vars to be fixed nor obs to be dropped specified."
			STOP
		}
		local fix = 0
	}

	// Check numeric var drop exists
	local drop = 0
	local dropvar ""
	cap confirm numeric var drop
	if !_rc {
		local drop = 1
		local dropvar drop
	}

	// Store vars in Excel spreadsheet
	qui des _all, varlist
	local vars_excel = r(varlist)
	
	// If matchmiss is specified, match using missing values of matching vars
	if "`matchmiss'" == "matchmiss" {
		local i = 1
		local 1 "`matching'"
		tempfile temp_1
		save `temp_`1''
	}
	// If matchmiss isn't specified, get combos of non-missing matching vars
	else {
		tempvar temp
		qui gen `temp' = ""
		foreach v of varlist `matching' {
			qui replace `temp' = ustrtrim(`temp' + " `v'") if !missing(`v')
		}
		assert !missing(`temp')

		// Save one dataset per combo of matching vars
		levelsof `temp', local(matchingcombos)
		tokenize `"`matchingcombos'"'
		local i = 1
		foreach combo of local matchingcombos {
			preserve
			local vlist: list matching - combo
			foreach v of local vlist {
				drop if !missing(`v')
			}
			foreach v of varlist `combo' {
				drop if missing(`v')
			}
			keep `combo' new_* reason* `dropvar' `vars_excel'
			tempfile temp_`i'
			save `temp_`i''
			local ++i
			restore
		}
	}

	// Match master w/ fixing data
	use `temp_master', clear
	gen totmatch = 0
	forvalues x = 1/`=`i'-1' {
		merge m:1 ``x'' using `temp_`x'', keep(master match)

		// Fix vars
		if `fix' {
			foreach v of varlist `fixing' {

				// If fixing file isn't a crosswalk,
				// check that old and new values differ.
				if "`xwalk'" != "xwalk" {
					assert `v' != new_`v' if _merge == 3
				}
				replace `v' = new_`v' if _merge == 3
				drop new_`v'
			}

			// Count times obs is matched
			replace totmatch = totmatch + 1 if _merge == 3

			// Drop obs
			if `drop' {
				drop if drop == 1
			}
			drop reason* `dropvar'
		}
		drop _merge
	}

	// Check no obs is matched once or never
	assert inlist(totmatch, 0, 1)

	// Check all observations have been matched
	if "`matchall'" == "matchall" {
		assert totmatch == 1
	}
	drop totmatch
end	
